Public Class cCapComune
    Inherits Connessione
    Private _isNew As Boolean = True
    Private _cap As String
    Private _codiceComune As String
    Private _localita As String
    Private _comprensorio As String
    Private _provincia As String
    Private _stato As String
    Private _idLocalita As String
    Public ReadOnly Property IsNew() As Boolean
        Get
            Return Me._isNew
        End Get
    End Property
    Public Property cap() As String
        Get
            Return _cap
        End Get
        Set(ByVal Value As String)
            _cap = Value
        End Set
    End Property

    Public Property codiceComune() As String
        Get
            Return _codiceComune
        End Get
        Set(ByVal Value As String)
            _codiceComune = Value
        End Set
    End Property

    Public Property localita() As String
        Get
            Return _localita
        End Get
        Set(ByVal Value As String)
            _localita = Value
        End Set
    End Property
    Public Property comprensorio() As String
        Get
            Return _comprensorio
        End Get
        Set(ByVal Value As String)
            _comprensorio = Value
        End Set
    End Property
    Public Property provincia() As String
        Get
            Return _provincia
        End Get
        Set(ByVal Value As String)
            _provincia = Value
        End Set
    End Property
    Public Property stato() As String
        Get
            Return _stato
        End Get
        Set(ByVal Value As String)
            _stato = Value
        End Set
    End Property

    Public Property idLocalita() As String
        Get
            Return _idLocalita
        End Get
        Set(ByVal Value As String)
            _idLocalita = Value
            _isNew = False
        End Set
    End Property


    Public Function caricaLocalitaByProvincia(ByVal provincia As String) As DataTable
        Dim conn As New Connessione
        Dim dt As New DataTable

        If provincia <> String.Empty Then
            Dim sql As String = "select * from PostaCapLocalitaProvincia WHERE Provincia = '" & provincia & "'"
            dt = conn.getDataTable(sql)
            Return dt
        End If


        Return dt

    End Function

    Public Function caricaLocalitaByComune(ByVal idComune As String) As DataTable
        Dim conn As New Connessione
        Dim dt As New DataTable

        If idComune <> String.Empty Then
            Dim sql As String = "SELECT PostaCapLocalitaProvincia.Id,  PostaCapLocalitaProvincia.Localita " & _
                                "FROM PostaCapLocalitaProvincia INNER JOIN LocalitaComuni ON PostaCapLocalitaProvincia.Id = LocalitaComuni.idLocalita " & _
                                "WHERE LocalitaComuni.idComune = " & Integer.Parse(idComune)

            dt = conn.getDataTable(sql)
            Return dt
        End If


        Return dt

    End Function
    Public Function caricaComuniByComprensorio(ByVal idComp As String) As DataTable
        Dim conn As New Connessione
        Dim sql As String

        If idComp <> "" Then
            sql = "SELECT Comuni.Codice, Comuni.Descrizione FROM Comuni INNER JOIN ComuniComprensori ON dbo.Comuni.Codice = dbo.ComuniComprensori.IdComune WHERE ComuniComprensori.idComprensorio=" & idComp & " ORDER BY Comuni.Descrizione"
        Else
            sql = "SELECT Comuni.Codice, Comuni.Descrizione FROM Comuni INNER JOIN ComuniComprensori ON dbo.Comuni.Codice = dbo.ComuniComprensori.IdComune ORDER BY Comuni.Descrizione"

        End If
        Dim dt As DataTable = conn.getDataTable(sql)
        Return dt
    End Function

    Public Function caricaLocalitaElenco(ByVal descrizione As String, ByVal cap As String) As DataTable
        Dim conn As New Connessione
        Dim dt As DataTable
        Dim sql As String = "SELECT PostaCapLocalitaProvincia.Id, PostaCapLocalitaProvincia.CAP, PostaCapLocalitaProvincia.Localita, Comuni.Descrizione AS Comune, PostaCapLocalitaProvincia.Provincia, Comuni.Codice AS IdComune "
        sql += "FROM Comuni LEFT OUTER JOIN LocalitaComuni ON Comuni.Codice = LocalitaComuni.idComune RIGHT OUTER JOIN PostaCapLocalitaProvincia ON LocalitaComuni.idLocalita = PostaCapLocalitaProvincia.Id "

        If cap <> String.Empty Then
            sql += "WHERE PostaCapLocalitaProvincia.CAP='" & cap & "';"
            'Dim sql As String = "SELECT PostaCapLocalitaProvincia.Id, dbo.PostaCapLocalitaProvincia.CAP, dbo.PostaCapLocalitaProvincia.Localita, " & _
            '          "  dbo.PostaCapLocalitaProvincia.Provincia, dbo.ComuniComprensori.IdComprensorio, dbo.Comuni.Descrizione as DescComune,dbo.TipoComprensorio.codice, " & _
            '          " dbo.ComuniComprensori.IdComune,dbo.PostaCapLocalitaProvincia.LocalitaD FROM dbo.Comuni INNER JOIN " & _
            '          " dbo.ComuniComprensori ON dbo.Comuni.Codice = dbo.ComuniComprensori.IdComune  LEFT OUTER JOIN " & _
            '          "dbo.TipoComprensorio ON dbo.ComuniComprensori.IdComprensorio = dbo.TipoComprensorio.id RIGHT OUTER JOIN " & _
            '          "dbo.PostaCapLocalitaProvincia LEFT OUTER JOIN " & _
            '          "dbo.LocalitaComuni ON dbo.PostaCapLocalitaProvincia.Id = dbo.LocalitaComuni.idLocalita ON " & _
            '          "dbo.ComuniComprensori.IdComune = dbo.LocalitaComuni.idComune WHERE Cap = " & Integer.Parse(cap)
            'dt = conn.getDataTable(sql)
        ElseIf descrizione <> String.Empty Then

            'Dim sql As String = "SELECT PostaCapLocalitaProvincia.Id, dbo.PostaCapLocalitaProvincia.CAP, dbo.PostaCapLocalitaProvincia.Localita, " & _
            '           "  dbo.PostaCapLocalitaProvincia.Provincia, dbo.ComuniComprensori.IdComprensorio, dbo.Comuni.Descrizione as DescComune,dbo.TipoComprensorio.codice, " & _
            '           " dbo.ComuniComprensori.IdComune,dbo.PostaCapLocalitaProvincia.LocalitaD FROM dbo.Comuni INNER JOIN " & _
            '           " dbo.ComuniComprensori ON dbo.Comuni.Codice = dbo.ComuniComprensori.IdComune  LEFT OUTER JOIN " & _
            '           "dbo.TipoComprensorio ON dbo.ComuniComprensori.IdComprensorio = dbo.TipoComprensorio.id RIGHT OUTER JOIN " & _
            '           "dbo.PostaCapLocalitaProvincia LEFT OUTER JOIN " & _
            '           "dbo.LocalitaComuni ON dbo.PostaCapLocalitaProvincia.Id = dbo.LocalitaComuni.idLocalita ON " & _
            '           "dbo.ComuniComprensori.IdComune = dbo.LocalitaComuni.idComune
            'dt = conn.getDataTable(sql)
            'Return dt
            sql += "WHERE PostaCapLocalitaProvincia.Localita LIKE '" & descrizione & "%' ORDER BY PostaCapLocalitaProvincia.Localita;"
        End If
        dt = conn.getDataTable(sql)
        Return dt

    End Function

    Public Function caricaComuni(ByVal provincia As String) As DataTable
        Dim conn As New Connessione
        Dim sql As String

        If provincia = "" Then
            sql = "select codice,descrizione + ' (' + provincia + ')' as descrizione from Comuni"
        Else
            sql = "select codice,descrizione + ' (' + provincia + ')' as descrizione from Comuni WHERE Provincia = '" & provincia & "'"
        End If
        Dim dt As DataTable = conn.getDataTable(sql)

        Return dt

    End Function

    Public Function caricaComuneTedesco(ByVal codice As String) As String
        Dim conn As New Connessione
        Dim sql As String = String.Empty

        sql = "select descrizioneD from Comuni where codice=" & codice

        Dim val As String = conn.getValore(sql)

        Return val

    End Function

    Public Function caricaComboComuni(ByVal provincia As String) As DataTable
        Dim conn As New Connessione
        Dim sql As String

        If Trim(provincia) = "" Then
            sql = "select null as codice, '<Seleziona una provincia>' as descrizione"
        Else
            sql = "select null as codice, '<Seleziona un comune>' as descrizione union select codice,descrizione + ' (' + provincia + ')' as descrizione from Comuni WHERE provincia = '" & provincia & "'"
        End If
        Dim dt As DataTable = conn.getDataTable(sql)
        Return dt

    End Function


    Public Function caricaLocalita(ByVal idLoc As String) As cCapComune
        Dim conn As New Connessione
        Dim sql As String = "SELECT PostaCapLocalitaProvincia.Id, PostaCapLocalitaProvincia.CAP, PostaCapLocalitaProvincia.Localita, dbo.PostaCapLocalitaProvincia.Provincia, " & _
        "ComuniComprensori.IdComprensorio, ComuniComprensori.IdComune FROM PostaCapLocalitaProvincia LEFT OUTER JOIN " & _
        "LocalitaComuni ON PostaCapLocalitaProvincia.Id = LocalitaComuni.idLocalita LEFT OUTER JOIN " & _
        "ComuniComprensori ON LocalitaComuni.idComune = ComuniComprensori.IdComune WHERE PostaCapLocalitaProvincia.Id=" & Integer.Parse(idLoc)
        sql = "SELECT PostaCapLocalitaProvincia.Id, PostaCapLocalitaProvincia.CAP, PostaCapLocalitaProvincia.Localita, Comuni.Descrizione AS Comune, PostaCapLocalitaProvincia.Provincia, Comuni.Codice AS IdComune "
        sql += "FROM Comuni LEFT OUTER JOIN LocalitaComuni ON Comuni.Codice = LocalitaComuni.idComune RIGHT OUTER JOIN PostaCapLocalitaProvincia ON LocalitaComuni.idLocalita = PostaCapLocalitaProvincia.Id "
        sql += "WHERE PostaCapLocalitaProvincia.Id=" & Integer.Parse(idLoc)
        Dim obj As New cCapComune

        If conn.getDataTable(sql).Rows.Count > 0 Then
            Dim dw As DataRow = conn.getDataTable(sql).Rows(0)
            obj.idLocalita = dw("Id")
            obj.cap = Utility.sNull(dw("Cap"))
            obj.localita = Utility.sNull(dw("Localita"))
            obj.codiceComune = Utility.sNull(dw("idComune"))
            ' obj.comprensorio = Utility.sNull(dw("idComprensorio"))
            obj.provincia = Utility.sNull(dw("Provincia"))
        End If

        Return obj
    End Function

    Public Function caricaLocalitaElenco() As DataTable
        Dim conn As New Connessione
        Dim sql As String
        '= "SELECT PostaCapLocalitaProvincia.Id, dbo.PostaCapLocalitaProvincia.CAP, dbo.PostaCapLocalitaProvincia.Localita, " & _
        '            "  dbo.PostaCapLocalitaProvincia.Provincia, dbo.ComuniComprensori.IdComprensorio, dbo.Comuni.Descrizione as DescComune,dbo.TipoComprensorio.codice, " & _
        '            " dbo.ComuniComprensori.IdComune,dbo.PostaCapLocalitaProvincia.LocalitaD FROM dbo.Comuni INNER JOIN " & _
        '            " dbo.ComuniComprensori ON dbo.Comuni.Codice = dbo.ComuniComprensori.IdComune  LEFT OUTER JOIN " & _
        '            "dbo.TipoComprensorio ON dbo.ComuniComprensori.IdComprensorio = dbo.TipoComprensorio.id RIGHT OUTER JOIN " & _
        '            "dbo.PostaCapLocalitaProvincia LEFT OUTER JOIN " & _
        '            "dbo.LocalitaComuni ON dbo.PostaCapLocalitaProvincia.Id = dbo.LocalitaComuni.idLocalita ON " & _
        '            "dbo.ComuniComprensori.IdComune = dbo.LocalitaComuni.idComune"

        sql = "SELECT PostaCapLocalitaProvincia.Id, PostaCapLocalitaProvincia.CAP, PostaCapLocalitaProvincia.Localita, Comuni.Descrizione AS Comune, PostaCapLocalitaProvincia.Provincia, Comuni.Codice AS IdComune "
        sql += "FROM Comuni LEFT OUTER JOIN LocalitaComuni ON Comuni.Codice = LocalitaComuni.idComune RIGHT OUTER JOIN PostaCapLocalitaProvincia ON LocalitaComuni.idLocalita = PostaCapLocalitaProvincia.Id "
        Dim dt As DataTable = conn.getDataTable(sql)
        Return dt

    End Function
    Public Function caricaLocalitaElencoPerProvincia(ByVal siglaProvincia As String) As DataTable
        Dim conn As New Connessione
        Dim sql As String = "SELECT PostaCapLocalitaProvincia.Id, dbo.PostaCapLocalitaProvincia.CAP, dbo.PostaCapLocalitaProvincia.Localita, " & _
                        "  dbo.PostaCapLocalitaProvincia.Provincia, dbo.ComuniComprensori.IdComprensorio, dbo.Comuni.Descrizione as DescComune,dbo.TipoComprensorio.codice, " & _
                        " dbo.ComuniComprensori.IdComune,dbo.PostaCapLocalitaProvincia.LocalitaD FROM dbo.Comuni INNER JOIN " & _
                        " dbo.ComuniComprensori ON dbo.Comuni.Codice = dbo.ComuniComprensori.IdComune  LEFT OUTER JOIN " & _
                        "dbo.TipoComprensorio ON dbo.ComuniComprensori.IdComprensorio = dbo.TipoComprensorio.id RIGHT OUTER JOIN " & _
                        "dbo.PostaCapLocalitaProvincia LEFT OUTER JOIN " & _
                        "dbo.LocalitaComuni ON dbo.PostaCapLocalitaProvincia.Id = dbo.LocalitaComuni.idLocalita ON " & _
                        "dbo.ComuniComprensori.IdComune = dbo.LocalitaComuni.idComune"

        sql = "SELECT PostaCapLocalitaProvincia.Id, PostaCapLocalitaProvincia.CAP, PostaCapLocalitaProvincia.Localita, Comuni.Descrizione AS Comune, PostaCapLocalitaProvincia.Provincia, Comuni.Codice AS IdComune "
        sql += "FROM Comuni LEFT OUTER JOIN LocalitaComuni ON Comuni.Codice = LocalitaComuni.idComune RIGHT OUTER JOIN PostaCapLocalitaProvincia ON LocalitaComuni.idLocalita = PostaCapLocalitaProvincia.Id "
        sql += "WHERE PostaCapLocalitaProvincia.Provincia = '" & siglaProvincia & "';"
        Dim dt As DataTable = conn.getDataTable(sql)
        Return dt

    End Function
    Public Function elencoComuniCombo(ByVal comune As String) As DataTable
        Dim sql As String = "select 0 as codice, '' as descrizione union  select codice, descrizione + ' - ' + case when descrizioneD is null then '' else descrizioneD end  +  ' (' + provincia + ')'  as descrizione  from comuni  where (descrizione LIKE '" & comune & "%') "
        Return getDataTable(sql)
    End Function

    Public Function caricaComprensori() As DataTable
        Dim conn As New Connessione
        Dim sql As String = "SELECT [id], [codice], [descrizione] FROM [TipoComprensorio]"
        Dim dt As DataTable = conn.getDataTable(sql)

        Return dt

    End Function

    Public Function caricaComboComprensori() As DataTable
        Dim conn As New Connessione
        Dim sql As String = "select null as codice,'' as descrizione union select id as codice,codice + ' - ' + descrizione as descrizione from TipoComprensorio"
        Dim dt As DataTable = conn.getDataTable(sql)

        Return dt

    End Function

    Public Function caricaProvince() As DataTable
        Dim conn As New Connessione
        Dim sql As String = "select null as codice,'' as descrizione union select * from Province order by descrizione"
        Dim dt As DataTable = conn.getDataTable(sql)

        Return dt

    End Function

    Public Function caricaStati() As DataTable
        Dim conn As New Connessione
        Dim sql As String = "select null as codice,'' as descrizione union select * from Stati"
        Dim dt As DataTable = conn.getDataTable(sql)

        Return dt

    End Function

    Public Function salva(ByVal objCap As cCapComune)
        If (objCap.IsNew) Then
            objCap.localita = insertPostaCapLocalitaProvincia(objCap.cap, objCap.localita, objCap.provincia)
        Else
            updateCapLocalitaProvincia(Integer.Parse(objCap.idLocalita), objCap.cap, objCap.localita, objCap.provincia)
        End If
        If objCap.idLocalita <> "" And objCap.codiceComune <> "" Then
            updateLocalitaComuni(objCap)
        End If
    End Function
    Public Sub updateCapLocalitaProvincia(ByVal Id As Integer, ByVal sCap As String, ByVal sLocalita As String, ByVal sProvincia As String)
        Dim oCmd As System.Data.SqlClient.SqlCommand
        oCmd = New System.Data.SqlClient.SqlCommand("UPDATE PostaCapLocalitaProvincia SET cap=@Cap, localita=@Localita, provincia=@Provincia WHERE Id=@Id;")
        oCmd.Parameters.Add("@Cap", SqlDbType.Char, 5)
        oCmd.Parameters.Add("@Localita", SqlDbType.VarChar, 50)
        oCmd.Parameters.Add("@Provincia", SqlDbType.Char, 2)
        oCmd.Parameters.Add("@ID", SqlDbType.Int, 4)
        oCmd.Parameters("@Cap").Value = Connessione.stringToDbField(sCap)
        oCmd.Parameters("@Localita").Value = Connessione.stringToDbField(sLocalita)
        oCmd.Parameters("@Provincia").Value = Connessione.stringToDbField(sProvincia)
        oCmd.Parameters("@Id").Value = Id
        Dim cn As New Connessione
        cn.ExecuteNonQuerySqlCommand(oCmd)
    End Sub
    Public Function insertPostaCapLocalitaProvincia(ByVal sCap As String, ByVal sLocalita As String, ByVal sProvincia As String) As String
        Dim i As Integer
        Dim oCmd As System.Data.SqlClient.SqlCommand
        Dim sId As String
        oCmd = New System.Data.SqlClient.SqlCommand("INSERT INTO PostaCapLocalitaProvincia (cap, localita, provincia) VALUES (@Cap,@Localita,@Provincia);")
        oCmd.Parameters.Add("@Cap", SqlDbType.Char, 5)
        oCmd.Parameters.Add("@Localita", SqlDbType.VarChar, 50)
        oCmd.Parameters.Add("@Provincia", SqlDbType.Char, 2)
        oCmd.Parameters("@Cap").Value = Connessione.stringToDbField(sCap)
        oCmd.Parameters("@Localita").Value = Connessione.stringToDbField(sLocalita)
        oCmd.Parameters("@Provincia").Value = Connessione.stringToDbField(sProvincia)
        Dim cn As New Connessione
        i = cn.eseguiSqlCommandInsertAndGetIdentity(oCmd)
        sId = IIf(i < 1, String.Empty, i)
        Return sId
    End Function
    Public Sub updateLocalitaComuni(ByVal objCap As cCapComune)
        Dim conn As New Connessione
        Dim sqlDel, sqlIns As String
        Dim r As Boolean
        Dim dbTrans As Data.SqlClient.SqlTransaction
        Dim cmdDelete As Data.SqlClient.SqlCommand
        Dim cmdInsert As Data.SqlClient.SqlCommand
        r = True
        Try
            dbTrans = conn.getConnection().BeginTransaction()
            sqlDel = "DELETE FROM LocalitaComuni WHERE idLocalita = " & objCap.idLocalita & " AND idComune=" & objCap.codiceComune
            cmdDelete = New Data.SqlClient.SqlCommand(sqlDel)
            sqlIns = "INSERT INTO LocalitaComuni (idLocalita,idComune) VALUES (" & objCap.idLocalita & "," & objCap.codiceComune & ")"
            cmdInsert = New Data.SqlClient.SqlCommand(sqlIns)
            cmdDelete.Connection = dbTrans.Connection
            cmdDelete.Transaction = dbTrans
            cmdInsert.Connection = dbTrans.Connection
            cmdInsert.Transaction = dbTrans
            cmdDelete.ExecuteNonQuery()
            cmdInsert.ExecuteNonQuery()
        Catch sqlEx As SqlClient.SqlException
            r = False
        Catch ex As Exception
            r = False
        Finally
            If (r) Then
                dbTrans.Commit()
            Else
                dbTrans.Rollback()
            End If
        End Try
    End Sub

    Public Function deleteLocalitaComune(ByVal idLoc As String, ByVal idCom As String)
        Dim conn As New Connessione
        Dim sql As String

        sql = "DELETE FROM LocalitaComuni WHERE idLocalita = " & idLoc & " AND idComune=" & idCom

        conn.eseguiSql(sql)

    End Function




    Public Function insertCapComuni(ByVal objCap As cCapComune)
        Dim conn As New Connessione
        Dim sql As String = "INSERT INTO CapComuni VALUES ('" & objCap.cap & "'," & objCap.idLocalita & "," & objCap.codiceComune & ", '" & objCap.provincia & "'," & objCap.comprensorio & "," & objCap.stato & ")"

        conn.eseguiSql(sql)


    End Function

    Public Function insertLocalitaComuni(ByVal objCap As cCapComune)
        Dim conn As New Connessione
        Dim sql As String = "INSERT INTO LocalitaComuni VALUES (" & objCap.idLocalita & "," & objCap.codiceComune & ")"

        conn.eseguiSql(sql)


    End Function

    Public Function insertComuniComprensori(ByVal objCap As cCapComune)
        Dim conn As New Connessione
        Dim sql As String = "INSERT INTO ComuniComprensori VALUES (" & objCap.codiceComune & "," & objCap.comprensorio & ")"

        conn.eseguiSql(sql)


    End Function

    Public Function controlloCap(ByVal objCap As String) As Boolean
        Dim conn As New Connessione
        Dim sql As String = "SELECT * FROM CapComuni WHERE Cap='" + objCap + "'"
        Dim dt As DataTable = conn.getDataTable(sql)

        If dt.Rows.Count > 0 Then
            Return True
        Else
            Return False
        End If

    End Function

    Public Function insertComprensorio(ByVal codice As String, ByVal descrizione As String)
        Dim conn As New Connessione
        Dim sql As String = "INSERT INTO TipoComprensorio VALUES ('" & codice & "','" & descrizione & "')"

        conn.eseguiSql(sql)
    End Function

    Public Function eliminaComprensorio(ByVal id As Integer)
        Dim conn As New Connessione
        Dim sql As String = "DELETE FROM TipoComprensorio WHERE id=" & id
        conn.eseguiSql(sql)
    End Function
End Class
